package com.snmp.smn.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.snmp.smn.beans.Agent;
import com.snmp.smn.beans.Device;
import com.snmp.smn.utils.Utilities;

public class DBHelper {
	protected static Connection connection = null;

	public static void init(String host, String port, String db, String username, String password) throws Exception {
		Utilities.log("Connecting to " + host + ":" + port + "/" + db);
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://" + host + ":" + port + "/" + db;
		connection = DriverManager.getConnection(url, username, password);
		
		Utilities.log("Successfully connected to " + host + ":" + port + "/" + db);
	}
	
	public static void end() throws Exception {
		connection.close();
	}
	
	private static ResultSet executeQuery(String query) throws Exception {
		Utilities.log("Executing query : " + query);
		
		Statement statement = connection.createStatement();
		return statement.executeQuery(query);
	}
	
	private static void executeUpdate(String query) throws Exception {
		Utilities.log("Executing query : " + query);
		
		Statement statement = connection.createStatement();
		statement.executeUpdate(query);
		statement.close();
	}
	
	public static List<Agent> getAgents() throws Exception {
		String query = "select * from rt_agents where enabled = 'Y'";
		ResultSet resultSet = executeQuery(query);
		
		List<Agent> agents = new ArrayList<Agent>();
		while(resultSet.next()) {
			Agent agent = new Agent(resultSet.getInt("agent_id"), resultSet.getString("agent_name"),
									resultSet.getString("agent_ip"), resultSet.getInt("agent_port"));
			agents.add(agent);
		}
		
		return agents;
	}
	
	public static List<Device> getDevices() throws Exception {
		String query = "select * from rt_devices where enabled = 'Y'";
		ResultSet resultSet = executeQuery(query);
		
		List<Device> devices = new ArrayList<Device>();
		while(resultSet.next()) {
			Device device = new Device(resultSet.getInt("device_id"), resultSet.getString("device_name"),
										resultSet.getString("device_ip"), resultSet.getInt("device_port"),
										resultSet.getString("device_type"), resultSet.getString("command"));
			devices.add(device);
		}
		
		return devices;
	}

	public static void insertSuccessfulActivity(int agentId, String username) throws Exception {
		StringBuffer query = new StringBuffer("insert into lt_agents_activity ");
		query.append("(agent_id, username, time, status) values (");
		query.append(agentId).append(", ");
		query.append("'").append(username).append("', ");
		query.append("sysdate(), 'ONLINE')");

		executeUpdate(query.toString());
	}

	public static void updateSuccessfulAgentStatus(int agentId) throws Exception {
		StringBuffer query = new StringBuffer("insert into ot_agent_status ");
		query.append("(agent_id, last_ping, last_success_ping, status) values (");
		query.append(agentId).append(", ");
		query.append("sysdate(), sysdate(), 'ONLINE') ");
		query.append("on duplicate key update ");
		query.append("last_ping = sysdate(), last_success_ping = sysdate(), status='ONLINE'");
		
		executeUpdate(query.toString());
	}

	public static void insertFailedActivity(int agentId) throws Exception {
		StringBuffer query = new StringBuffer("insert into lt_agents_activity ");
		query.append("(agent_id, time, status) values (");
		query.append(agentId).append(", ");
		query.append("sysdate(), 'OFFLINE')");

		executeUpdate(query.toString());
	}

	public static void updateFailedAgentStatus(int agentId) throws Exception {
		StringBuffer query = new StringBuffer("insert into ot_agent_status ");
		query.append("(agent_id, last_ping, status) values (");
		query.append(agentId).append(", ");
		query.append("sysdate(), 'OFFLINE') ");
		query.append("on duplicate key update ");
		query.append("last_ping = sysdate(), status='OFFLINE'");
		
		executeUpdate(query.toString());
	}
	
	public static void insertSuccessfulCheck(int deviceId) throws Exception {
		StringBuffer query = new StringBuffer("insert into lt_devices_activity ");
		query.append("(device_id, last_retry, info) values (");
		query.append(deviceId).append(", ");
		query.append("sysdate(), 'SUCCESS')");

		executeUpdate(query.toString());
	}

	public static void updateSuccessfulDeviceStatus(int deviceId) throws Exception {
		StringBuffer query = new StringBuffer("insert into ot_device_status ");
		query.append("(device_id, last_check, last_success_check, status) values (");
		query.append(deviceId).append(", ");
		query.append("sysdate(), sysdate(), 'SUCCESS') ");
		query.append("on duplicate key update ");
		query.append("last_check = sysdate(), last_success_check = sysdate(), status='SUCCESS'");
		
		executeUpdate(query.toString());
	}
	
	public static void insertFailedCheck(int deviceId) throws Exception {
		StringBuffer query = new StringBuffer("insert into lt_devices_activity ");
		query.append("(device_id, last_retry, info) values (");
		query.append(deviceId).append(", ");
		query.append("sysdate(), 'FAILED')");

		executeUpdate(query.toString());		
	}

	public static void updateFailedDeviceStatus(int deviceId) throws Exception {
		StringBuffer query = new StringBuffer("insert into ot_device_status ");
		query.append("(device_id, last_check, status) values (");
		query.append(deviceId).append(", ");
		query.append("sysdate(), 'FAILED') ");
		query.append("on duplicate key update ");
		query.append("last_check = sysdate(), status='FAILED'");
		
		executeUpdate(query.toString());
	}
	
	public static List<String> getEmailAddresses() throws Exception {
		String query = "select email from rt_notifications where enabled = 'Y'";
		ResultSet resultSet = executeQuery(query);
		
		List<String> emailAddresses = new ArrayList<String>();
		while(resultSet.next()) {
			emailAddresses.add(resultSet.getString("email"));
		}
		
		return emailAddresses;
	}
	
	public static Agent getAgentByIP(String deviceIP) throws Exception {
		String query = "select * from rt_agents where agent_ip = '" + deviceIP + "'";
		ResultSet resultSet = executeQuery(query);
		
		while(resultSet.next()) {
			return new Agent(resultSet.getInt("agent_id"), resultSet.getString("agent_name"),
									resultSet.getString("agent_ip"), resultSet.getInt("agent_port"));
		}

		return null;
	}
}
